<?php

namespace Home\Service;

require __DIR__ . '/../Common/tcpdf/tcpdf.php';

use Home\Common\FIdConst;

/**
 * 销售订单Service
 *
 * @author 李静波
 */
class SOBillService extends PSIBaseService {
	private $LOG_CATEGORY = "销售订单";

	/**
	 * 生成新的销售订单号
	 */
	private function genNewBillRef() {
		$bs = new BizConfigService();
		$pre = $bs->getSOBillRefPre();
		
		$mid = date("Ymd");
		
		$sql = "select ref from t_so_bill where ref like '%s' order by ref desc limit 1";
		$data = M()->query($sql, $pre . $mid . "%");
		$sufLength = 3;
		$suf = str_pad("1", $sufLength, "0", STR_PAD_LEFT);
		if ($data) {
			$ref = $data[0]["ref"];
			$nextNumber = intval(substr($ref, strlen($pre . $mid))) + 1;
			$suf = str_pad($nextNumber, $sufLength, "0", STR_PAD_LEFT);
		}
		
		return $pre . $mid . $suf;
	}

	/**
	 * 获得销售订单主表信息列表
	 */
	public function sobillList($params) {
		if ($this->isNotOnline()) {
			return $this->emptyResult();
		}
		
		$start = $params["start"];
		$limit = $params["limit"];
		
		$billStatus = $params["billStatus"];
		$wsbillStatus = $params["wsbillStatus"];
		$ref = $params["ref"];
		$fromDT = $params["fromDT"];
		$toDT = $params["toDT"];
		$customerId = $params["customerId"];
		$receivingType = $params["receivingType"];
		
		$tel = $params["tel"];
		$goodsId = $params["goodsId"];
		
		$db = M();
		
		$queryParams = array();
		
		$result = array();
		$sql = "select s.id, s.ref, s.bill_status, s.goods_money, s.tax, s.money_with_tax,
					c.name as customer_name, s.contact, s.tel, s.fax, s.deal_address,
					s.deal_date, s.receiving_type, s.bill_memo, s.date_created,
					o.full_name as org_name, u1.name as biz_user_name, u2.name as input_user_name,
					s.confirm_user_id, s.confirm_date, w.name as warehouse_name,
					s.freight, s.express_company_id, s.sales_mode_id, s.deal_province, s.deal_city, 
					s.deal_district, s.print_count, s.last_print_dt
				from t_so_bill s, t_customer c, t_org o, t_user u1, t_user u2, t_warehouse w
				where (s.customer_id = c.id) and (s.org_id = o.id)
					and (s.biz_user_id = u1.id) and (s.input_user_id = u2.id) and (s.warehouse_id = w.id)";
		
		$ds = new DataOrgService();
		$rs = $ds->buildSQL(FIdConst::SALE_ORDER, "s");
		if ($rs) {
			$sql .= " and " . $rs[0];
			$queryParams = $rs[1];
		}
		
		if ($billStatus != - 1) {
			$sql .= " and (s.bill_status = %d) ";
			$queryParams[] = $billStatus;
		}
		
		if ($wsbillStatus != - 1) {
			$sql .= " and (s.id in (
						select sows.so_id
						from t_so_ws sows, t_ws_bill wsBill
						where sows.ws_id = wsBill.id and wsBill.bill_status = %d
					))";
			$queryParams[] = $wsbillStatus;
		}
		
		if ($ref) {
			$sql .= " and (s.ref like '%s') ";
			$queryParams[] = "%$ref%";
		}
		if ($fromDT) {
			$sql .= " and (s.deal_date >= '%s')";
			$queryParams[] = $fromDT;
		}
		if ($toDT) {
			$sql .= " and (s.deal_date <= '%s')";
			$queryParams[] = $toDT;
		}
		if ($customerId) {
			$sql .= " and (s.customer_id = '%s')";
			$queryParams[] = $customerId;
		}
		if ($receivingType != - 1) {
			$sql .= " and (s.receiving_type = %d) ";
			$queryParams[] = $receivingType;
		}
		if ($tel) {
			$sql .= " and (s.tel like '%s') ";
			$queryParams[] = "%{$tel}%";
		}
		if ($goodsId) {
			$sql .= " and (s.id in (
						select sobill_id
						from t_so_bill_detail
						where goods_id = '%s'
					))";
			$queryParams[] = $goodsId;
		}
		$sql .= " order by s.deal_date desc, s.ref desc 
				  limit %d , %d";
		$queryParams[] = $start;
		$queryParams[] = $limit;
		$data = $db->query($sql, $queryParams);
		foreach ( $data as $i => $v ) {
			$result[$i]["id"] = $v["id"];
			$result[$i]["ref"] = $v["ref"];
			$result[$i]["billStatus"] = $v["bill_status"];
			$result[$i]["dealDate"] = $this->toYMD($v["deal_date"]);
			$result[$i]["customerName"] = $v["customer_name"];
			$result[$i]["contact"] = $v["contact"];
			$result[$i]["tel"] = $v["tel"];
			$result[$i]["fax"] = $v["fax"];
			$result[$i]["goodsMoney"] = $v["goods_money"];
			$result[$i]["tax"] = $v["tax"];
			$result[$i]["moneyWithTax"] = $v["money_with_tax"];
			$result[$i]["receivingType"] = $v["receiving_type"];
			$result[$i]["billMemo"] = $v["bill_memo"];
			$result[$i]["bizUserName"] = $v["biz_user_name"];
			$result[$i]["orgName"] = $v["org_name"];
			$result[$i]["inputUserName"] = $v["input_user_name"];
			$result[$i]["dateCreated"] = $v["date_created"];
			$result[$i]["warehouseName"] = $v["warehouse_name"];
			$result[$i]["freight"] = $v["freight"];
			
			$dealProvince = $v["deal_province"];
			$dealCity = $v["deal_city"];
			$dealDistrict = $v["deal_district"];
			if (! $dealProvince) {
				// 这是之前的旧数据，
				$result[$i]["dealAddress"] = $v["deal_address"];
			} else {
				$result[$i]["dealAddress"] = $dealProvince . $dealCity . $dealDistrict . $v["deal_address"];
			}
			
			$confirmUserId = $v["confirm_user_id"];
			if ($confirmUserId) {
				$sql = "select name from t_user where id = '%s' ";
				$d = $db->query($sql, $confirmUserId);
				if ($d) {
					$result[$i]["confirmUserName"] = $d[0]["name"];
					$result[$i]["confirmDate"] = $v["confirm_date"];
				}
			}
			
			$ecId = $v["express_company_id"];
			if ($ecId) {
				$sql = "select name from t_express_company where id = '%s' ";
				$d = $db->query($sql, $ecId);
				if ($d) {
					$result[$i]["expressCompany"] = $d[0]["name"];
				}
			}
			
			$smId = $v["sales_mode_id"];
			if ($smId) {
				$sql = "select mode_type, name from t_sales_mode where id = '%s' ";
				$d = $db->query($sql, $smId);
				if ($d) {
					$result[$i]["salesMode"] = $this->modeTypeToLabel($d[0]["mode_type"]) . " - " . $d[0]["name"];
				}
			}
			
			// 查询出库单状态
			$id = $v["id"];
			$sql = "select w.bill_status
					from t_ws_bill w, t_so_ws s
					where w.id = s.ws_id and s.so_id = '%s' ";
			$d = $db->query($sql, $id);
			if ($d) {
				$s = $d[0]["bill_status"];
				$result[$i]["wsbillStatus"] = $s;
			} else {
				$result[$i]["wsbillStatus"] = - 1;
			}
			
			// 打印次数和时间
			$result[$i]["printDT"] = $v["last_print_dt"];
			$c = $v["print_count"];
			if ($c > 0) {
				$result[$i]["printCount"] = $c;
			}
		}
		
		$sql = "select count(*) as cnt
				from t_so_bill s, t_customer c, t_org o, t_user u1, t_user u2, t_warehouse w
				where (s.customer_id = c.id) and (s.org_id = o.id)
					and (s.biz_user_id = u1.id) and (s.input_user_id = u2.id)
					and (s.warehouse_id = w.id)
				";
		$queryParams = array();
		$ds = new DataOrgService();
		$rs = $ds->buildSQL(FIdConst::SALE_ORDER, "s");
		if ($rs) {
			$sql .= " and " . $rs[0];
			$queryParams = $rs[1];
		}
		if ($billStatus != - 1) {
			$sql .= " and (s.bill_status = %d) ";
			$queryParams[] = $billStatus;
		}
		if ($wsbillStatus != - 1) {
			$sql .= " and (s.id in (
						select sows.so_id
						from t_so_ws sows, t_ws_bill wsBill
						where sows.ws_id = wsBill.id and wsBill.bill_status = %d
					))";
			$queryParams[] = $wsbillStatus;
		}
		if ($ref) {
			$sql .= " and (s.ref like '%s') ";
			$queryParams[] = "%$ref%";
		}
		if ($fromDT) {
			$sql .= " and (s.deal_date >= '%s')";
			$queryParams[] = $fromDT;
		}
		if ($toDT) {
			$sql .= " and (s.deal_date <= '%s')";
			$queryParams[] = $toDT;
		}
		if ($customerId) {
			$sql .= " and (s.customer_id = '%s')";
			$queryParams[] = $customerId;
		}
		if ($receivingType != - 1) {
			$sql .= " and (s.receiving_type = %d) ";
			$queryParams[] = $receivingType;
		}
		if ($tel) {
			$sql .= " and (s.tel like '%s') ";
			$queryParams[] = "%{$tel}%";
		}
		if ($goodsId) {
			$sql .= " and (s.id in (
						select sobill_id
						from t_so_bill_detail
						where goods_id = '%s'
					))";
			$queryParams[] = $goodsId;
		}
		$data = $db->query($sql, $queryParams);
		$cnt = $data[0]["cnt"];
		
		return array(
				"dataList" => $result,
				"totalCount" => $cnt
		);
	}

	/**
	 * 获得销售订单的信息
	 */
	public function soBillInfo($params) {
		if ($this->isNotOnline()) {
			return $this->emptyResult();
		}
		$id = $params["id"];
		
		$result = array();
		
		$cs = new BizConfigService();
		$result["taxRate"] = $cs->getTaxRate();
		
		$db = M();
		
		if ($id) {
			// 编辑销售订单
			$sql = "select s.ref, s.deal_date, s.deal_address, s.customer_id,
						c.name as customer_name, s.contact, s.tel, s.fax,
						s.org_id, o.full_name, s.biz_user_id, u.name as biz_user_name,
						s.receiving_type, s.bill_memo, s.bill_status, 
						s.warehouse_id, w.name as warehouse_name,
						s.sales_mode_id, s.express_company_id, s.freight,
						s.deal_province, s.deal_city, s.deal_district
					from t_so_bill s, t_customer c, t_user u, t_org o, t_warehouse w
					where s.id = '%s' and s.customer_Id = c.id
						and s.biz_user_id = u.id
						and s.org_id = o.id and s.warehouse_id = w.id";
			$data = $db->query($sql, $id);
			if ($data) {
				$v = $data[0];
				$result["ref"] = $v["ref"];
				$result["dealDate"] = $this->toYMD($v["deal_date"]);
				$result["dealAddress"] = $v["deal_address"];
				$result["customerId"] = $v["customer_id"];
				$result["customerName"] = $v["customer_name"];
				$result["contact"] = $v["contact"];
				$result["tel"] = $v["tel"];
				$result["fax"] = $v["fax"];
				$result["orgId"] = $v["org_id"];
				$result["orgFullName"] = $v["full_name"];
				$result["bizUserId"] = $v["biz_user_id"];
				$result["bizUserName"] = $v["biz_user_name"];
				$result["receivingType"] = $v["receiving_type"];
				$result["billMemo"] = $v["bill_memo"];
				$result["billStatus"] = $v["bill_status"];
				$result["warehouseId"] = $v["warehouse_id"];
				$result["warehouseName"] = $v["warehouse_name"];
				$result["salesModeId"] = $v["sales_mode_id"];
				$result["expressCompanyId"] = $v["express_company_id"];
				$result["freight"] = $v["freight"];
				
				$dealProvince = $v["deal_province"];
				if (! $dealProvince) {
					// 这是代码改动之前的旧数据，那么就从客户资料里面查询省市县
					$customerId = $v["customer_id"];
					$sql = "select province, city, district from t_customer where id = '%s' ";
					$d = $db->query($sql, $customerId);
					if ($d) {
						$result["dealProvince"] = $d[0]["province"];
						$result["dealCity"] = $d[0]["city"];
						$result["dealDistrict"] = $d[0]["district"];
					}
				} else {
					$result["dealProvince"] = $v["deal_province"];
					$result["dealCity"] = $v["deal_city"];
					$result["dealDistrict"] = $v["deal_district"];
				}
				
				// 明细表
				$sql = "select s.id, s.goods_id, g.code, g.name, g.spec, s.goods_count, s.goods_price, s.goods_money,
					s.tax_rate, s.tax, s.money_with_tax, u.name as unit_name,
						s.qc_begin_dt, s.expiration
				from t_so_bill_detail s, t_goods g, t_goods_unit u
				where s.sobill_id = '%s' and s.goods_id = g.id and g.unit_id = u.id
				order by s.show_order";
				$items = array();
				$data = $db->query($sql, $id);
				
				foreach ( $data as $i => $v ) {
					$items[$i]["goodsId"] = $v["goods_id"];
					$items[$i]["goodsCode"] = $v["code"];
					$items[$i]["goodsName"] = $v["name"];
					$items[$i]["goodsSpec"] = $v["spec"];
					$items[$i]["goodsCount"] = $v["goods_count"];
					$items[$i]["goodsPrice"] = $v["goods_price"];
					$items[$i]["goodsMoney"] = $v["goods_money"];
					$items[$i]["taxRate"] = intval($v["tax_rate"]);
					$items[$i]["tax"] = $v["tax"];
					$items[$i]["moneyWithTax"] = $v["money_with_tax"];
					$items[$i]["unitName"] = $v["unit_name"];
					
					$qcBeginDT = $this->toYmdForQC($v["qc_begin_dt"]);
					if ($qcBeginDT) {
						$items[$i]["qcBeginDT"] = $qcBeginDT;
					}
					$expiration = $v["expiration"];
					if ($expiration > 0) {
						$items[$i]["expiration"] = $expiration;
					}
				}
				
				$result["items"] = $items;
			}
		} else {
			// 新建销售订单
			$us = new UserService();
			$result["bizUserId"] = $us->getLoginUserId();
			$result["bizUserName"] = $us->getLoginUserName();
			
			$sql = "select o.id, o.full_name
					from t_org o, t_user u
					where o.id = u.org_id and u.id = '%s' ";
			$data = $db->query($sql, $us->getLoginUserId());
			if ($data) {
				$result["orgId"] = $data[0]["id"];
				$result["orgFullName"] = $data[0]["full_name"];
			}
		}
		
		// 销售收入方式
		$sql = "select id, mode_type, name
				from t_sales_mode
				order by mode_type";
		$data = $db->query($sql);
		$sm = array();
		foreach ( $data as $i => $v ) {
			$sm[$i]["id"] = $v["id"];
			$sm[$i]["text"] = $this->modeTypeToLabel($v["mode_type"]) . " - " . $v["name"];
		}
		$result["salesModeList"] = $sm;
		
		// 快递公司
		$sql = "select id, name
				from t_express_company
				order by name";
		$data = $db->query($sql);
		$ec = array();
		foreach ( $data as $i => $v ) {
			$ec[$i]["id"] = $v["id"];
			$ec[$i]["text"] = $v["name"];
		}
		$result["expressCompanyList"] = $ec;
		
		return $result;
	}

	/**
	 * 新增或编辑销售订单
	 */
	public function editSOBill($json) {
		if ($this->isNotOnline()) {
			return $this->notOnlineError();
		}
		
		$bill = json_decode(html_entity_decode($json), true);
		if ($bill == null) {
			return $this->bad("传入的参数错误，不是正确的JSON格式");
		}
		
		$db = M();
		
		$db->startTrans();
		
		$checkInv = $bill["checkInv"];
		
		$id = $bill["id"];
		$dealDate = $bill["dealDate"];
		if (! $this->dateIsValid($dealDate)) {
			$db->rollback();
			return $this->bad("交货日期不正确");
		}
		
		$customerId = $bill["customerId"];
		$cs = new CustomerService();
		if (! $cs->customerExists($customerId, $db)) {
			$db->rollback();
			return $this->bad("客户不存在");
		}
		$orgId = $bill["orgId"];
		$us = new UserService();
		if (! $us->orgExists($orgId, $db)) {
			$db->rollback();
			return $this->bad("组织机构不存在");
		}
		$bizUserId = $bill["bizUserId"];
		if (! $us->userExists($bizUserId, $db)) {
			$db->rollback();
			return $this->bad("业务员不存在");
		}
		$warehouseId = $bill["warehouseId"];
		$ws = new WarehouseService();
		if (! $ws->warehouseExists($warehouseId, $db)) {
			$db->rollback();
			return $this->bad("出库仓库不存在");
		}
		$receivingType = $bill["receivingType"];
		$contact = $bill["contact"];
		$tel = $bill["tel"];
		$fax = $bill["fax"];
		$dealAddress = $bill["dealAddress"];
		$billMemo = $bill["billMemo"];
		
		$dealProvince = $bill["dealProvince"];
		$dealCity = $bill["dealCity"];
		$dealDistrict = $bill["dealDistrict"];
		
		$freight = $bill["freight"];
		if (! $freight) {
			$freight = 0;
		}
		
		$expressCompanyId = $bill["expressCompanyId"];
		$sql = "select count(*) as cnt from t_express_company where id = '%s' ";
		$data = $db->query($sql, $expressCompanyId);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$db->rollback();
			return $this->bad("快递公司不存在");
		}
		
		$salesModeId = $bill["salesModeId"];
		$sql = "select count(*) as cnt from t_sales_mode where id = '%s' ";
		$data = $db->query($sql, $salesModeId);
		$cnt = $data[0]["cnt"];
		if ($cnt == 0) {
			$db->rollback();
			return $this->bad("收入方式不存在");
		}
		
		$items = $bill["items"];
		
		foreach ( $items as $i => $v ) {
			$index = $i + 1;
			
			$goodsId = $v["goodsId"];
			if (! $goodsId) {
				continue;
			}
			
			$count = $v["goodsCount"];
			if ($count <= 0) {
				$db->rollback();
				return $this->bad("第{$index}条商品记录的销售数量需要大于0");
			}
			
			$price = floatval($v["goodsPrice"]);
			if ($price <= 0) {
				$db->rollback();
				return $this->bad("第{$index}条商品记录的销售单价需要大于0");
			}
			
			// 检查商品是否停用
			$sql = "select code, disabled from t_goods where id = '%s' ";
			$data = $db->query($sql, $goodsId);
			if (! $data) {
				$db->rollback();
				return $this->bad("第{$index}条商品在基础数据中不存在");
			} else {
				$code = $data[0]["code"];
				$disabled = $data[0]["disabled"];
				if ($disabled != 0) {
					$db->rollback();
					return $this->bad("第{$index}条商品(编码: {$code})已经停用，不能被销售");
				}
			}
			
			if ($checkInv) {
				$qcBeginDT = $v["qcBeginDT"];
				if (! $qcBeginDT) {
					$qcBeginDT = "1970-01-01";
				}
				$expiration = $v["expiration"];
				if (! $expiration) {
					$expiration = 0;
				}
				
				// 查询库存
				$sql = "select balance_count from t_inventory_lot
						where warehouse_id = '%s' and goods_id = '%s'
							and begin_dt = '%s' and expiration = %d ";
				$d = $db->query($sql, $warehouseId, $goodsId, $qcBeginDT, $expiration);
				$balanceCount = 0;
				if ($d) {
					$balanceCount = $d[0]["balance_count"];
				}
				
				// 计算所有的出库单中预留库存
				$sql = "select sum(sd.goods_count) as sum_goods_count
						from t_so_bill s, t_so_bill_detail sd
						where s.warehouse_id = '%s' and s.id = sd.sobill_id
							and sd.goods_id = '%s' and sd.qc_begin_dt = '%s' 
							and sd.expiration = %d  
							and s.id in (
								select sows.so_id
								from t_so_ws sows, t_ws_bill wsBill
								where sows.ws_id = wsBill.id and wsBill.bill_status = 0
						)";
				$d = $db->query($sql, $warehouseId, $goodsId, $qcBeginDT, $expiration);
				$sumCount = $d[0]["sum_goods_count"];
				
				$sumCount += $count;
				
				if ($sumCount > $balanceCount) {
					$index = $i + 1;
					$info = "第{$index}条商品的库存不足，请确认是否保存订单？";
					$result = $this->bad($info);
					$result["checkingInv"] = true;
					
					return $result;
				}
			}
		}
		
		$idGen = new IdGenService();
		
		$companyId = $us->getCompanyId();
		if (! $companyId) {
			$db->rollback();
			return $this->bad("所属公司不存在");
		}
		
		$log = null;
		if ($id) {
			// 编辑
			$sql = "select ref, data_org, bill_status, company_id from t_so_bill where id = '%s' ";
			$data = $db->query($sql, $id);
			if (! $data) {
				$db->rollback();
				return $this->bad("要编辑的销售订单不存在");
			}
			$ref = $data[0]["ref"];
			$dataOrg = $data[0]["data_org"];
			$companyId = $data[0]["company_id"];
			$billStatus = $data[0]["bill_status"];
			if ($billStatus != 0) {
				$db->rollback();
				return $this->bad("当前销售订单已经审核，不能再编辑");
			}
			
			$sql = "delete from t_so_bill_detail where sobill_id = '%s' ";
			$rc = $db->execute($sql, $id);
			if ($rc === false) {
				$db->rollback();
				return $this->sqlError(__LINE__);
			}
			
			foreach ( $items as $i => $v ) {
				$goodsId = $v["goodsId"];
				if (! $goodsId) {
					continue;
				}
				$goodsCount = $v["goodsCount"];
				$goodsPrice = $v["goodsPrice"];
				$goodsMoney = $v["goodsMoney"];
				$taxRate = $v["taxRate"];
				$tax = $v["tax"];
				$moneyWithTax = $v["moneyWithTax"];
				
				$qcBeginDT = $v["qcBeginDT"];
				if (! $qcBeginDT) {
					$qcBeginDT = "1970-01-01";
				}
				$expiration = $v["expiration"];
				if (! $expiration) {
					$expiration = 0;
				}
				$qcEndDT = date("Y-m-d", strtotime($qcBeginDT . " +$expiration day"));
				
				$sql = "insert into t_so_bill_detail(id, date_created, goods_id, goods_count, goods_money,
							goods_price, sobill_id, tax_rate, tax, money_with_tax, ws_count, left_count, 
							show_order, data_org, company_id, qc_begin_dt, qc_end_dt, expiration)
						values ('%s', now(), '%s', %d, %f,
							%f, '%s', %d, %f, %f, 0, %d, %d, '%s', '%s', '%s', '%s', %d)";
				$rc = $db->execute($sql, $idGen->newId(), $goodsId, $goodsCount, $goodsMoney, 
						$goodsPrice, $id, $taxRate, $tax, $moneyWithTax, $goodsCount, $i, $dataOrg, 
						$companyId, $qcBeginDT, $qcEndDT, $expiration);
				if ($rc === false) {
					$db->rollback();
					return $this->sqlError(__LINE__);
				}
			}
			
			// 同步主表的金额合计字段
			$sql = "select sum(goods_money) as sum_goods_money, sum(tax) as sum_tax, 
							sum(money_with_tax) as sum_money_with_tax
						from t_so_bill_detail
						where sobill_id = '%s' ";
			$data = $db->query($sql, $id);
			$sumGoodsMoney = $data[0]["sum_goods_money"];
			if (! $sumGoodsMoney) {
				$sumGoodsMoney = 0;
			}
			$sumTax = $data[0]["sum_tax"];
			if (! $sumTax) {
				$sumTax = 0;
			}
			$sumMoneyWithTax = $data[0]["sum_money_with_tax"];
			if (! $sumMoneyWithTax) {
				$sumMoneyWithTax = 0;
			}
			
			$sql = "update t_so_bill
					set goods_money = %f, tax = %f, money_with_tax = %f,
						deal_date = '%s', customer_id = '%s',
						deal_address = '%s', contact = '%s', tel = '%s', fax = '%s',
						org_id = '%s', biz_user_id = '%s', receiving_type = %d,
						bill_memo = '%s', input_user_id = '%s', date_created = now(),
						warehouse_id = '%s', express_company_id = '%s', freight = %f,
						sales_mode_id = '%s', deal_province = '%s', deal_city = '%s',
						deal_district = '%s'
					where id = '%s' ";
			$rc = $db->execute($sql, $sumGoodsMoney, $sumTax, $sumMoneyWithTax, $dealDate, 
					$customerId, $dealAddress, $contact, $tel, $fax, $orgId, $bizUserId, 
					$receivingType, $billMemo, $us->getLoginUserId(), $warehouseId, 
					$expressCompanyId, $freight, $salesModeId, $dealProvince, $dealCity, 
					$dealDistrict, $id);
			if ($rc === false) {
				$db->rollback();
				return $this->sqlError(__LINE__);
			}
			
			$log = "编辑销售订单，单号：{$ref}";
		} else {
			// 新建销售订单
			
			$id = $idGen->newId();
			$ref = $this->genNewBillRef();
			
			$us = new UserService();
			$dataOrg = $us->getLoginUserDataOrg();
			
			// 主表
			$sql = "insert into t_so_bill(id, ref, bill_status, deal_date, biz_dt, org_id, biz_user_id,
							goods_money, tax, money_with_tax, input_user_id, customer_id, contact, tel, fax,
							deal_address, bill_memo, receiving_type, date_created, data_org, company_id,
							warehouse_id, express_company_id, freight, sales_mode_id, deal_province,
							deal_city, deal_district)
						values ('%s', '%s', 0, '%s', '%s', '%s', '%s', 
							0, 0, 0, '%s', '%s', '%s', '%s', '%s', 
							'%s', '%s', %d, now(), '%s', '%s', 
							'%s', '%s', %f, '%s', '%s', '%s', '%s')";
			$rc = $db->execute($sql, $id, $ref, $dealDate, $dealDate, $orgId, $bizUserId, 
					$us->getLoginUserId(), $customerId, $contact, $tel, $fax, $dealAddress, 
					$billMemo, $receivingType, $dataOrg, $companyId, $warehouseId, $expressCompanyId, 
					$freight, $salesModeId, $dealProvince, $dealCity, $dealDistrict);
			if ($rc === false) {
				$db->rollback();
				return $this->sqlError(__LINE__);
			}
			
			// 明细记录
			foreach ( $items as $i => $v ) {
				$goodsId = $v["goodsId"];
				if (! $goodsId) {
					continue;
				}
				$goodsCount = $v["goodsCount"];
				$goodsPrice = $v["goodsPrice"];
				$goodsMoney = $v["goodsMoney"];
				$taxRate = $v["taxRate"];
				$tax = $v["tax"];
				$moneyWithTax = $v["moneyWithTax"];
				
				$qcBeginDT = $v["qcBeginDT"];
				if (! $qcBeginDT) {
					$qcBeginDT = "1970-01-01";
				}
				$expiration = $v["expiration"];
				if (! $expiration) {
					$expiration = 0;
				}
				$qcEndDT = date("Y-m-d", strtotime($qcBeginDT . " +$expiration day"));
				
				$sql = "insert into t_so_bill_detail(id, date_created, goods_id, goods_count, goods_money,
								goods_price, sobill_id, tax_rate, tax, money_with_tax, ws_count, left_count, 
								show_order, data_org, company_id, qc_begin_dt, qc_end_dt, expiration)
							values ('%s', now(), '%s', %d, %f,
								%f, '%s', %d, %f, %f, 0, %d, %d, '%s', '%s', '%s', '%s', %d)";
				$rc = $db->execute($sql, $idGen->newId(), $goodsId, $goodsCount, $goodsMoney, 
						$goodsPrice, $id, $taxRate, $tax, $moneyWithTax, $goodsCount, $i, $dataOrg, 
						$companyId, $qcBeginDT, $qcEndDT, $expiration);
				if ($rc === false) {
					$db->rollback();
					return $this->sqlError(__LINE__);
				}
			}
			
			// 同步主表的金额合计字段
			$sql = "select sum(goods_money) as sum_goods_money, sum(tax) as sum_tax, 
							sum(money_with_tax) as sum_money_with_tax
						from t_so_bill_detail
						where sobill_id = '%s' ";
			$data = $db->query($sql, $id);
			$sumGoodsMoney = $data[0]["sum_goods_money"];
			if (! $sumGoodsMoney) {
				$sumGoodsMoney = 0;
			}
			$sumTax = $data[0]["sum_tax"];
			if (! $sumTax) {
				$sumTax = 0;
			}
			$sumMoneyWithTax = $data[0]["sum_money_with_tax"];
			if (! $sumMoneyWithTax) {
				$sumMoneyWithTax = 0;
			}
			
			$sql = "update t_so_bill
						set goods_money = %f, tax = %f, money_with_tax = %f
						where id = '%s' ";
			$rc = $db->execute($sql, $sumGoodsMoney, $sumTax, $sumMoneyWithTax, $id);
			if ($rc === false) {
				$db->rollback();
				return $this->sqlError(__LINE__);
			}
			
			$log = "新建销售订单，单号：{$ref}";
		}
		
		// 分摊运费
		$sql = "select s.id, s.goods_id, s.goods_count, g.weight
				from t_so_bill_detail s, t_goods g
				where s.sobill_id = '%s' and s.goods_id = g.id
				order by s.show_order";
		$items = $db->query($sql, $id);
		
		// 按重量分摊运费，先计算所有商品的总重量
		$totalWeight = 0;
		foreach ( $items as $item ) {
			$w = $item["weight"];
			if (! $w) {
				$w = 1;
			}
			
			$c = $item["goods_count"];
			
			$itemWeight = $w * $c;
			
			$totalWeight += $itemWeight;
		}
		
		$count = count($items);
		$tempFreight = 0;
		foreach ( $items as $i => $v ) {
			$w = $v["weight"];
			if (! $w) {
				$w = 1;
			}
			
			$c = $v["goods_count"];
			$itemWeight = $w * $c;
			
			$f = 0;
			if ($i < $count - 1) {
				$f = $freight * ($itemWeight / $totalWeight);
				$tempFreight += $f;
			} else {
				// 最后一个商品，分摊的时候，把剩余的运费全部分摊给它
				$f = $freight - $tempFreight;
			}
			
			$sql = "update t_so_bill_detail
					set freight = %f
					where id = '%s' ";
			$rc = $db->execute($sql, $f, $v["id"]);
			if ($rc === false) {
				$db->rollback();
				return $this->sqlError(__LINE__);
			}
		}
		
		// 同步客户资料中地址
		$sql = "select address from t_customer where id = '%s' ";
		$data = $db->query($sql, $customerId);
		if ($data) {
			$oldAddres = $data[0]["address"];
			if ($oldAddres != $dealAddress) {
				$sql = "update t_customer
						set address = '%s'
						where id = '%s' ";
				$rc = $db->execute($sql, $dealAddress, $customerId);
				if ($rc === false) {
					$db->rollback();
					return $this->sqlError(__LINE__);
				}
			}
		}
		
		// 记录业务日志
		if ($log) {
			$bs = new BizlogService();
			$bs->insertBizlog($log, $this->LOG_CATEGORY);
		}
		
		$db->commit();
		
		return $this->ok($id);
	}

	/**
	 * 获得销售订单的明细信息
	 */
	public function soBillDetailList($params) {
		if ($this->isNotOnline()) {
			return $this->emptyResult();
		}
		
		$id = $params["id"];
		$db = M();
		
		$sql = "select s.id, g.code, g.name, g.spec, s.goods_count, s.goods_price, s.goods_money,
					s.tax_rate, s.tax, s.money_with_tax, u.name as unit_name,
					s.qc_begin_dt, s.expiration, s.qc_end_dt, s.freight
				from t_so_bill_detail s, t_goods g, t_goods_unit u
				where s.sobill_id = '%s' and s.goods_id = g.id and g.unit_id = u.id
				order by s.show_order";
		$result = array();
		$data = $db->query($sql, $id);
		
		foreach ( $data as $i => $v ) {
			$result[$i]["id"] = $v["id"];
			$result[$i]["goodsCode"] = $v["code"];
			$result[$i]["goodsName"] = $v["name"];
			$result[$i]["goodsSpec"] = $v["spec"];
			$result[$i]["goodsCount"] = $v["goods_count"];
			$result[$i]["goodsPrice"] = $v["goods_price"];
			$result[$i]["goodsMoney"] = $v["goods_money"];
			$result[$i]["taxRate"] = $v["tax_rate"];
			$result[$i]["tax"] = $v["tax"];
			$result[$i]["moneyWithTax"] = $v["money_with_tax"];
			$result[$i]["unitName"] = $v["unit_name"];
			
			$qcBeginDT = $this->toYmdForQC($v["qc_begin_dt"]);
			if ($qcBeginDT) {
				$result[$i]["qcBeginDT"] = $qcBeginDT;
			}
			
			$expiration = $v["expiration"];
			if ($expiration > 0) {
				$result[$i]["expiration"] = $expiration;
			}
			
			$qcEndDT = $this->toYmdForQC($v["qc_end_dt"]);
			if ($qcEndDT) {
				$result[$i]["qcEndDT"] = $qcEndDT;
			}
			
			$freight = $v["freight"];
			if ($freight) {
				$result[$i]["freight"] = $freight;
			}
		}
		
		return $result;
	}

	/**
	 * 删除销售订单
	 */
	public function deleteSOBill($params) {
		if ($this->isNotOnline()) {
			return $this->notOnlineError();
		}
		
		$id = $params["id"];
		$db = M();
		
		$db->startTrans();
		
		$sql = "select ref, bill_status from t_so_bill where id = '%s' ";
		$data = $db->query($sql, $id);
		if (! $data) {
			$db->rollback();
			return $this->bad("要删除的销售订单不存在");
		}
		$ref = $data[0]["ref"];
		$billStatus = $data[0]["bill_status"];
		if ($billStatus > 0) {
			$db->rollback();
			return $this->bad("销售订单(单号：{$ref})已经审核，不能被删除");
		}
		
		$sql = "delete from t_so_bill_detail where sobill_id = '%s' ";
		$rc = $db->execute($sql, $id);
		if ($rc === false) {
			$db->rollback();
			return $this->sqlError(__LINE__);
		}
		
		$sql = "delete from t_so_bill where id = '%s' ";
		$rc = $db->execute($sql, $id);
		if ($rc === false) {
			$db->rollback();
			return $this->sqlError(__LINE__);
		}
		
		$log = "删除销售订单，单号：{$ref}";
		$bs = new BizlogService();
		$bs->insertBizlog($log, $this->LOG_CATEGORY);
		
		$db->commit();
		
		return $this->ok();
	}

	/**
	 * 审核销售订单
	 */
	public function commitSOBill($params) {
		if ($this->isNotOnline()) {
			return $this->notOnlineError();
		}
		
		$id = $params["id"];
		$checkInv = $params["checkInv"];
		
		$db = M();
		
		$db->startTrans();
		
		$sql = "select ref, bill_status, customer_id, warehouse_id from t_so_bill where id = '%s' ";
		$data = $db->query($sql, $id);
		if (! $data) {
			$db->rollback();
			return $this->bad("要审核的销售订单不存在");
		}
		$ref = $data[0]["ref"];
		$billStatus = $data[0]["bill_status"];
		$customerId = $data[0]["customer_id"];
		$warehouseId = $data[0]["warehouse_id"];
		if ($billStatus > 0) {
			$db->rollback();
			return $this->bad("销售订单(单号：$ref)已经被审核，不能再次审核");
		}
		
		if ($checkInv == 1) {
			$sql = "select goods_id, qc_begin_dt, expiration, goods_count
					from t_so_bill_detail
					where sobill_id = '%s'
					order by show_order";
			$items = $db->query($sql, $id);
			
			foreach ( $items as $i => $v ) {
				$goodsId = $v["goods_id"];
				$count = $v["goods_count"];
				
				$qcBeginDT = $this->toYmdForQC($v["qc_begin_dt"]);
				if ($qcBeginDT == null) {
					$qcBeginDT = "1970-01-01";
				}
				$expiration = $v["expiration"];
				if (! $expiration) {
					$expiration = 0;
				}
				
				// 查询库存
				$sql = "select balance_count from t_inventory_lot
						where warehouse_id = '%s' and goods_id = '%s'
							and begin_dt = '%s' and expiration = %d ";
				$d = $db->query($sql, $warehouseId, $goodsId, $qcBeginDT, $expiration);
				$balanceCount = 0;
				if ($d) {
					$balanceCount = $d[0]["balance_count"];
				}
				
				// 计算所有的出库单中预留库存
				$sql = "select sum(sd.goods_count) as sum_goods_count
						from t_so_bill s, t_so_bill_detail sd
						where s.warehouse_id = '%s' and s.id = sd.sobill_id
							and sd.goods_id = '%s' and sd.qc_begin_dt = '%s'
							and sd.expiration = %d
							and s.id in (
								select sows.so_id
								from t_so_ws sows, t_ws_bill wsBill
								where sows.ws_id = wsBill.id and wsBill.bill_status = 0
						)";
				$d = $db->query($sql, $warehouseId, $goodsId, $qcBeginDT, $expiration);
				$sumCount = $d[0]["sum_goods_count"];
				
				$sumCount += $count;
				
				if ($sumCount > $balanceCount) {
					$index = $i + 1;
					$info = "第{$index}条商品的库存不足，请确认是否审核销售订单？";
					$result = $this->bad($info);
					$result["checkingInv"] = true;
					
					return $result;
				}
			}
		}
		
		$sql = "update t_so_bill
					set bill_status = 1000,
						confirm_user_id = '%s',
						confirm_date = now()
					where id = '%s' ";
		$us = new UserService();
		$rc = $db->execute($sql, $us->getLoginUserId(), $id);
		if ($rc === false) {
			$db->rollback();
			return $this->sqlError(__LINE__);
		}
		
		// 更新客户-商品-价格
		$sql = "select goods_id, goods_price
				from t_so_bill_detail
				where sobill_id = '%s'
				order by show_order";
		$items = $db->query($sql, $id);
		$idGen = new IdGenService();
		foreach ( $items as $v ) {
			$goodsId = $v["goods_id"];
			$price = $v["goods_price"];
			
			$sql = "delete from t_customer_saleprice
					where customer_id = '%s' and goods_id = '%s' ";
			$rc = $db->execute($sql, $customerId, $goodsId);
			if ($rc === false) {
				$db->rollback();
				return $this->sqlError(__LINE__);
			}
			
			$sql = "insert into t_customer_saleprice (id, customer_id, goods_id, sale_price)
					values ('%s', '%s', '%s', %f)";
			$rc = $db->execute($sql, $idGen->newId($db), $customerId, $goodsId, $price);
			if ($rc === false) {
				$db->rollback();
				return $this->sqlError(__LINE__);
			}
		}
		
		// 自动创建销售出库单
		$ws = new WSBillService();
		$rc = $ws->createWSBill($id, $db);
		if ($rc) {
			$db->rollback();
			return $rc;
		}
		
		// 记录业务日志
		$log = "审核销售订单，单号：{$ref}";
		$bs = new BizlogService();
		$bs->insertBizlog($log, $this->LOG_CATEGORY);
		
		$db->commit();
		
		return $this->ok($id);
	}

	/**
	 * 取消销售订单审核
	 */
	public function cancelConfirmSOBill($params) {
		if ($this->isNotOnline()) {
			return $this->notOnlineError();
		}
		
		$id = $params["id"];
		$db = M();
		
		$db->startTrans();
		
		$sql = "select ref, bill_status from t_so_bill where id = '%s' ";
		$data = $db->query($sql, $id);
		if (! $data) {
			$db->rollback();
			return $this->bad("要取消审核的销售订单不存在");
		}
		$ref = $data[0]["ref"];
		$billStatus = $data[0]["bill_status"];
		if ($billStatus > 1000) {
			$db->rollback();
			return $this->bad("销售订单(单号:{$ref})不能取消审核");
		}
		
		$sql = "update t_so_bill
					set bill_status = 0, confirm_user_id = null, confirm_date = null
					where id = '%s' ";
		$rc = $db->execute($sql, $id);
		if ($rc === false) {
			$db->rollback();
			return $this->sqlError(__LINE__);
		}
		
		// 删除自动生成的销售出库单
		$sql = "select ws_id
				from t_so_ws
				where so_id = '%s' ";
		$data = $db->query($sql, $id);
		if ($data) {
			$wsId = $data[0]["ws_id"];
			
			$sql = "select ref, bill_status from t_ws_bill where id = '%s' ";
			$data = $db->query($sql, $wsId);
			if ($data) {
				$wsRef = $data[0]["ref"];
				$status = $data[0]["bill_status"];
				if ($status > 0) {
					$db->rollback();
					return $this->bad("销售订单生成的销售出库单(单号：{$wsRef})已经提交出库，所以不能取消审核");
				}
				
				$sql = "delete from t_ws_bill_detail where wsbill_id = '%s' ";
				$rc = $db->execute($sql, $wsId);
				if ($rc === false) {
					$db->rollback();
					return $this->sqlError(__LINE__);
				}
				
				$sql = "delete from t_ws_bill where id = '%s' ";
				$rc = $db->execute($sql, $wsId);
				if ($rc === false) {
					$db->rollback();
					return $this->sqlError(__LINE__);
				}
				
				$sql = "delete from t_so_ws where ws_id = '%s' and so_id = '%s' ";
				$rc = $db->execute($sql, $wsId, $id);
				if ($rc === false) {
					$db->rollback();
					return $this->sqlError(__LINE__);
				}
			}
		}
		
		// 记录业务日志
		$log = "取消审核销售订单，单号：{$ref}";
		$bs = new BizlogService();
		$bs->insertBizlog($log, $this->LOG_CATEGORY);
		
		$db->commit();
		
		return $this->ok($id);
	}

	private function getPdfInstance() {
		$pdf = new \TCPDF("L", "mm", array(
				240,
				95
		), true, 'UTF-8', false);
		
		$pdf->SetCreator(PDF_CREATOR);
		
		$pdf->SetMargins(PDF_MARGIN_LEFT, 10, PDF_MARGIN_RIGHT);
		$pdf->SetHeaderMargin(0);
		$pdf->SetFooterMargin(5);
		
		$pdf->SetAutoPageBreak(false, PDF_MARGIN_BOTTOM);
		
		$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
		
		return $pdf;
	}

	/**
	 * 销售订单生成pdf
	 */
	public function pdf($params) {
		if ($this->isNotOnline()) {
			return;
		}
		
		$ref = $params["ref"];
		$db = M();
		$sql = "select w.id, w.biz_dt, c.name as customer_name, c.taobao_nickname,
					  h.name as warehouse_name,
					  w.confirm_date as biz_dt,
						w.deal_address, w.tel
					from t_so_bill w, t_customer c, t_user u, t_warehouse h
					where w.customer_id = c.id and w.biz_user_id = u.id
					  and w.warehouse_id = h.id
					  and w.ref = '%s' ";
		$data = $db->query($sql, $ref);
		if (! $data) {
			return;
		}
		
		$id = $data[0]["id"];
		
		$bill["bizDT"] = date("Y-m-d h:i", strtotime($data[0]["biz_dt"]));
		$bill["customerName"] = $data[0]["customer_name"];
		$bill["warehouseName"] = $data[0]["warehouse_name"];
		$bill["dealAddress"] = $data[0]["deal_address"];
		$bill["tel"] = $data[0]["tel"];
		$bill["nickname"] = $data[0]["taobao_nickname"];
		
		// 明细表
		$sql = "select g.code, g.name, g.spec, d.qc_begin_dt, d.goods_count,
					d.goods_price, d.goods_money
				from t_so_bill_detail d, t_goods g, t_goods_unit u
				where d.sobill_id = '%s' and d.goods_id = g.id and g.unit_id = u.id
				order by d.show_order";
		$data = $db->query($sql, $id);
		$items = array();
		foreach ( $data as $i => $v ) {
			$items[$i]["goodsCode"] = $v["code"];
			$items[$i]["goodsName"] = $v["name"];
			$items[$i]["goodsSpec"] = $v["spec"];
			$items[$i]["goodsCount"] = $v["goods_count"];
			$items[$i]["goodsPrice"] = $v["goods_price"];
			$items[$i]["goodsMoney"] = $v["goods_money"];
			
			$qcBeginDT = $this->toYmdForQC($v["qc_begin_dt"]);
			if ($qcBeginDT) {
				$items[$i]["qcBeginDT"] = $qcBeginDT;
			}
		}
		$bill["items"] = $items;
		
		$pdf = $this->getPdfInstance();
		$pdf->SetTitle("销售订单：{$ref}");
		
		$pdf->setHeaderFont(Array(
				"stsongstdlight",
				"",
				10
		));
		
		$pdf->setFooterFont(Array(
				"stsongstdlight",
				"",
				10
		));
		
		$pdf->SetAutoPageBreak(true, 5);
		
		$cs = new BizConfigService();
		$pdf->SetAuthor($cs->getProductionName());
		
		$pdf->SetFont("stsongstdlight", "", 10);
		$pdf->AddPage();
		
		$html = '
				<table>
					<tr>
						<td>销售订单号 : ' . $ref . '</td>
						<td>买家昵称 : ' . $bill["nickname"] . '</td>
					</tr>
					<tr>
						<td>客户 : ' . $bill["customerName"] . '</td>
						<td><div style="font-size:12">联系电话 : ' . $bill["tel"] . '</div></td></tr>
					<tr><td colspan="2">客户地址 : ' . $bill["dealAddress"] . '</td></tr>
					<tr><td>审核时间 : ' . $bill["bizDT"] . '</td><td>出库仓库 : ' . $bill["warehouseName"] . '</td></tr>
				</table>
				';
		$pdf->writeHTML($html);
		
		$html = '<table border="1" cellpadding="1">
					<tr>
						<td style="width:30px">序号</td>
						<td align="center" style="width:60px">商品编号</td>
						<td align="center" style="width:60px">数量</td>
						<td align="center" style="width:200px">商品名称</td>
						<td align="center">规格型号</td>
						<td align="center" style="width:60px">生产日期</td>
						<td align="center" style="width:60px">单价</td>
						<td align="center" style="width:60px">小计</td>
					</tr>
				';
		
		$totalCount = 0;
		$totalMoney = 0;
		foreach ( $bill["items"] as $i => $v ) {
			$totalCount += $v["goodsCount"];
			$totalMoney += $v["goodsMoney"];
			$index = $i + 1;
			
			$html .= '<tr>';
			$html .= '<td align="center">' . $index . '</td>';
			$html .= '<td>' . $v["goodsCode"] . '</td>';
			$html .= '<td align="center">' . $v["goodsCount"] . '</td>';
			$html .= '<td>' . $v["goodsName"] . '</td>';
			$html .= '<td>' . $v["goodsSpec"] . '</td>';
			$html .= "<td>" . $v["qcBeginDT"] . "</td>";
			$html .= '<td align="right">' . $v["goodsPrice"] . '</td>';
			$html .= '<td align="right">' . $v["goodsMoney"] . '</td>';
			$html .= '</tr>';
		}
		
		// 合计
		$html .= '<tr>
					<td>合计</td>
					<td></td>
					<td align="center">' . $totalCount . '</td>
					<td></td>
					<td></td>
					<td></td>
					<td></td>
					<td align="rigth">￥' . number_format($totalMoney, 2) . '</td>
				</tr>';
		
		$html .= '</table>';
		
		$pdf->writeHTML($html, true, false, true, false, '');
		
		$pdf->Output("{$ref}.pdf", "I");
	}

	private function modeTypeToLabel($modeType) {
		switch ($modeType) {
			case 1 :
				return "现金收入";
			case 2 :
				return "转账收入";
			case 3 :
				return "POS刷卡";
			case 4 :
				return "赊销";
			default :
				return "";
		}
	}

	public function salesModeList() {
		if ($this->isNotOnline()) {
			return $this->emptyResult();
		}
		
		$db = M();
		$sql = "select id, mode_type, name
				from t_sales_mode
				order by mode_type";
		$result = array();
		
		$data = $db->query($sql);
		foreach ( $data as $i => $v ) {
			$result[$i]["id"] = $v["id"];
			$result[$i]["name"] = $v["name"];
			$result[$i]["modeTypeId"] = $v["mode_type"];
			$result[$i]["modeType"] = $this->modeTypeToLabel($v["mode_type"]);
		}
		return $result;
	}

	/**
	 * 新增或编辑销售收入方式
	 */
	public function editSalesMode($params) {
		if ($this->isNotOnline()) {
			return $this->notOnlineError();
		}
		
		$id = $params["id"];
		$modeType = intval($params["modeType"]);
		$name = $params["name"];
		
		if ($modeType < 1 || $modeType > 4) {
			return $this->bad("没有选择正确的分类");
		}
		
		$db = M();
		$db->startTrans();
		
		$us = new UserService();
		$dataOrg = $us->getLoginUserDataOrg();
		$companyId = $us->getCompanyId();
		
		$log = null;
		if ($id) {
			// 编辑
			
			$sql = "select count(*) as cnt from
					t_sales_mode
					where name = '%s' and id <> '%s' ";
			$data = $db->query($sql, $name, $id);
			$cnt = $data[0]["cnt"];
			if ($cnt > 0) {
				$db->rollback();
				return $this->bad("销售收入方式[$name]已经存在");
			}
			
			$sql = "update t_sales_mode
					set mode_type = %d, name = '%s'
					where id = '%s' ";
			$rc = $db->execute($sql, $modeType, $name, $id);
			if ($rc === false) {
				$db->rollback();
				return $this->sqlError(__LINE__);
			}
			
			$log = "编辑销售收入方式[$name]";
		} else {
			// 新建
			
			$sql = "select count(*) as cnt from 
					t_sales_mode 
					where name = '%s' ";
			$data = $db->query($sql, $name);
			$cnt = $data[0]["cnt"];
			if ($cnt > 0) {
				$db->rollback();
				return $this->bad("销售收入方式[$name]已经存在");
			}
			
			$idGen = new IdGenService();
			$id = $idGen->newId($db);
			
			$sql = "insert into t_sales_mode (id, mode_type, name, data_org, company_id)
					values ('%s', %d, '%s', '%s', '%s')";
			$rc = $db->execute($sql, $id, $modeType, $name, $dataOrg, $companyId);
			if ($rc === false) {
				$db->rollback();
				return $this->sqlError(__LINE__);
			}
			
			$log = "新建销售收入方式[$name]";
		}
		
		if ($log) {
			$bs = new BizlogService();
			$bs->insertBizlog($log, $this->LOG_CATEGORY);
		}
		
		$db->commit();
		
		return $this->ok($id);
	}

	public function deleteSalesMode($params) {
		if ($this->isNotOnline()) {
			return $this->notOnlineError();
		}
		
		$id = $params["id"];
		
		$db = M();
		
		$db->startTrans();
		
		$sql = "select name from t_sales_mode where id = '%s' ";
		$data = $db->query($sql, $id);
		if (! $data) {
			$db->rollback();
			return $this->bad("要删除的销售收入方式不存在");
		}
		
		$name = $data[0]["name"];
		
		$sql = "select count(*) as cnt from t_so_bill where sales_mode_id = '%s' ";
		$data = $db->query($sql, $id);
		$cnt = $data[0]["cnt"];
		if ($cnt > 0) {
			$db->rollback();
			return $this->bad("销售收入方式[$name]已经在销售订单中使用了，不能删除");
		}
		
		$sql = "delete from t_sales_mode where id = '%s' ";
		$rc = $db->execute($sql, $id);
		if ($rc === false) {
			$db->rollback();
			return $this->sqlError(__LINE__);
		}
		
		$log = "删除销售收入方式[$name]";
		$bs = new BizlogService();
		$bs->insertBizlog($log, $this->LOG_CATEGORY);
		
		$db->commit();
		
		return $this->ok();
	}

	public function getSOBillDataForPrint($ref) {
		if ($this->isNotOnline()) {
			return null;
		}
		
		$db = M();
		
		$sql = "select w.id, w.biz_dt, c.name as customer_name, c.taobao_nickname,
					  h.name as warehouse_name,
					  w.confirm_date as biz_dt,
						w.deal_address, w.tel, w.bill_memo, w.express_company_id,
						w.deal_province, w.deal_city, w.deal_district
					from t_so_bill w, t_customer c, t_user u, t_warehouse h
					where w.customer_id = c.id and w.biz_user_id = u.id
					  and w.warehouse_id = h.id
					  and w.ref = '%s' ";
		$data = $db->query($sql, $ref);
		if (! $data) {
			return null;
		}
		
		$bill = array();
		
		$id = $data[0]["id"];
		
		$bill["ref"] = $ref;
		$bill["bizDT"] = date("Y-m-d h:i", strtotime($data[0]["biz_dt"]));
		$bill["customerName"] = $data[0]["customer_name"];
		$bill["warehouseName"] = $data[0]["warehouse_name"];
		
		$dealAddress = $data[0]["deal_address"];
		$dealProvince = $data[0]["deal_province"];
		$dealCity = $data[0]["deal_city"];
		$dealDistrict = $data[0]["deal_district"];
		
		$bill["address"] = $dealProvince . $dealCity . $dealDistrict . $dealAddress;
		$bill["tel"] = $data[0]["tel"];
		$bill["nickname"] = $data[0]["taobao_nickname"];
		$bill["billMemo"] = $data[0]["bill_memo"];
		
		$ecId = $data[0]["express_company_id"];
		if ($ecId) {
			$sql = "select name from t_express_company where id = '%s' ";
			$data = $db->query($sql, $ecId);
			if ($data) {
				$bill["expressCompany"] = $data[0]["name"];
			}
		}
		
		// 明细表
		$sql = "select g.code, g.name, g.spec, d.qc_begin_dt, d.goods_count,
					d.goods_price, d.goods_money
				from t_so_bill_detail d, t_goods g, t_goods_unit u
				where d.sobill_id = '%s' and d.goods_id = g.id and g.unit_id = u.id
				order by d.show_order";
		$data = $db->query($sql, $id);
		$items = array();
		foreach ( $data as $i => $v ) {
			$items[$i]["goodsCode"] = $v["code"];
			$items[$i]["goodsName"] = $v["name"];
			$items[$i]["goodsSpec"] = $v["spec"];
			$items[$i]["goodsCount"] = $v["goods_count"];
			$items[$i]["goodsPrice"] = $v["goods_price"];
			$items[$i]["goodsMoney"] = $v["goods_money"];
			
			$qcBeginDT = $this->toYmdForQC($v["qc_begin_dt"]);
			if ($qcBeginDT) {
				$items[$i]["qcBeginDT"] = $qcBeginDT;
			}
		}
		$bill["items"] = $items;
		
		$totalCount = 0;
		$totalMoney = 0;
		foreach ( $items as $v ) {
			$totalCount += $v["goodsCount"];
			$totalMoney += $v["goodsMoney"];
		}
		
		$bill["totalCount"] = $totalCount;
		$bill["totalMoney"] = number_format($totalMoney, 2);
		
		$bill["printDT"] = date("Y-m-d H:i:s");
		return $bill;
	}

	public function updateLastPrintSoBillCount($soRef) {
		$db = M();
		$sql = "update t_so_bill
				set print_count = print_count + 1, last_print_dt = now()
				where ref = '%s'
				";
		$db->execute($sql, $soRef);
		
		return $this->ok();
	}

	public function getExpressPrintData($soRef) {
		$result = array(
				"hasData" => false
		);
		$db = M();
		$sql = "select express_company_id
				from t_so_bill
				where ref = '%s' ";
		$data = $db->query($sql, $soRef);
		if (! $data) {
			$result["msg"] = "销售订单没有录入快递公司";
			return $result;
		}
		
		$ecId = $data[0]["express_company_id"];
		
		$sql = "select id, page_top, page_left, page_width, page_height
				from t_express_bill_printpage
				where express_company_id = '%s' ";
		$data = $db->query($sql, $ecId);
		if (! $data) {
			$result["msg"] = "没有设置快递单打印模板";
			return $result;
		}
		
		$result["hasData"] = true;
		
		$pageId = $data[0]["id"];
		$result["pageTop"] = $data[0]["page_top"];
		$result["pageLeft"] = $data[0]["page_left"];
		$result["pageWidth"] = $data[0]["page_width"];
		$result["pageHeight"] = $data[0]["page_height"];
		
		$sql = "select name, item_top, item_left, item_width, item_height, font_size, is_bold
				from t_express_bill_printitem
				where page_id = '%s' ";
		$data = $db->query($sql, $pageId);
		$items = array();
		foreach ( $data as $v ) {
			$item = array(
					"top" => $v["item_top"],
					"left" => $v["item_left"],
					"width" => $v["item_width"],
					"height" => $v["item_height"],
					"fontSize" => $v["font_size"],
					"isBold" => $v["is_bold"]
			);
			
			$itemName = $v["name"];
			$item["value"] = $this->getValueByItemNameForExpressPrintData($soRef, $itemName, $db);
			
			$items[] = $item;
		}
		
		$result["items"] = $items;
		
		return $result;
	}

	private function getValueByItemNameForExpressPrintData($soRef, $itemName, $db) {
		if (! $db) {
			$db = M();
		}
		
		if ($itemName == "收件人-姓名") {
			$sql = "select c.name
					from t_so_bill s, t_customer c
					where s.customer_id = c.id and s.ref = '%s' ";
			$data = $db->query($sql, $soRef);
			if ($data) {
				return $data[0]["name"];
			}
		}
		
		if ($itemName == "收件人-淘宝昵称") {
			$sql = "select c.taobao_nickname
					from t_so_bill s, t_customer c
					where s.customer_id = c.id and s.ref = '%s' ";
			$data = $db->query($sql, $soRef);
			if ($data) {
				return $data[0]["taobao_nickname"];
			}
		}
		
		if ($itemName == "收件人-电话") {
			$sql = "select s.tel
					from t_so_bill s
					where s.ref = '%s' ";
			$data = $db->query($sql, $soRef);
			if ($data) {
				return $data[0]["tel"];
			}
		}
		
		if ($itemName == "收件人-省") {
			$sql = "select c.province
					from t_so_bill s, t_customer c
					where s.customer_id = c.id and s.ref = '%s' ";
			$data = $db->query($sql, $soRef);
			if ($data) {
				return $data[0]["province"];
			}
		}
		
		if ($itemName == "收件人-市") {
			$sql = "select c.city
					from t_so_bill s, t_customer c
					where s.customer_id = c.id and s.ref = '%s' ";
			$data = $db->query($sql, $soRef);
			if ($data) {
				return $data[0]["city"];
			}
		}
		
		if ($itemName == "收件人-区") {
			$sql = "select c.district
					from t_so_bill s, t_customer c
					where s.customer_id = c.id and s.ref = '%s' ";
			$data = $db->query($sql, $soRef);
			if ($data) {
				return $data[0]["district"];
			}
		}
		
		if ($itemName == "收件人-地址") {
			$sql = "select s.deal_province, s.deal_address
					from t_so_bill s
					where s.ref = '%s' ";
			$data = $db->query($sql, $soRef);
			if ($data) {
				$province = $data[0]["deal_province"];
				if ($province) {
					return $data[0]["deal_address"];
				}
			}
			
			// 旧数据
			$sql = "select c.address
					from t_so_bill s, t_customer c
					where s.customer_id = c.id and s.ref = '%s' ";
			$data = $db->query($sql, $soRef);
			if ($data) {
				return $data[0]["address"];
			}
		}
		
		if ($itemName == "商品概要") {
			$sql = "select g.name
					from t_so_bill s, t_so_bill_detail d, t_goods g
					where s.id = d.sobill_id and s.ref = '%s' 
						and d.goods_id = g.id
					order by d.show_order
					limit 1";
			$data = $db->query($sql, $soRef);
			if ($data) {
				return $data[0]["name"];
			}
		}
		
		if ($itemName == "发件人-姓名") {
			$us = new UserService();
			$companyId = $us->getCompanyId();
			
			$sql = "select value
					from t_config
					where company_id = '%s' and id = '发件人姓名' ";
			$data = $db->query($sql, $companyId);
			if ($data) {
				return $data[0]["value"];
			}
		}
		
		if ($itemName == "发件人-电话") {
			$us = new UserService();
			$companyId = $us->getCompanyId();
			
			$sql = "select value
					from t_config
					where company_id = '%s' and id = '发件人电话' ";
			$data = $db->query($sql, $companyId);
			if ($data) {
				return $data[0]["value"];
			}
		}
		
		if ($itemName == "发件人-地址") {
			$us = new UserService();
			$companyId = $us->getCompanyId();
			
			$sql = "select value
					from t_config
					where company_id = '%s' and id = '发件人地址' ";
			$data = $db->query($sql, $companyId);
			if ($data) {
				return $data[0]["value"];
			}
		}
		
		return "";
	}

	/**
	 * 获得某个客户对应的价格体系中的价格
	 */
	private function getPriceInPriceSystem($db, $customerId, $goodsId) {
		$sql = "select p.price
				from t_customer c, t_customer_category cc,
					t_goods_price p
				where c.id = '%s' and c.category_id = cc.id
					and p.goods_id = '%s' and p.ps_id = cc.ps_id";
		$data = $db->query($sql, $customerId, $goodsId);
		if (! $data) {
			return null;
		}
		
		return $data[0]["price"];
	}

	public function goodsBOMList($params) {
		if ($this->isNotOnline()) {
			return $this->emptyResult();
		}
		
		$id = $params["id"];
		$warehouseId = $params["warehouseId"];
		$customerId = $params["customerId"];
		$bomCount = intval($params["bomCount"]);
		
		$db = M();
		
		$result = array();
		
		$sql = "select b.sub_goods_id, b.sub_goods_count,
					g.code, g.name, g.spec, u.name as unit_name,
					g.sale_price
				from t_goods_bom b, t_goods g, t_goods_unit u
				where b.goods_id = '%s' and b.sub_goods_id = g.id
					and g.unit_id = u.id
				order by g.code";
		$subGoodsList = $db->query($sql, $id);
		$bomList = array();
		$index = 0;
		foreach ( $subGoodsList as $g ) {
			$goodsId = $g["sub_goods_id"];
			$subCount = $g["sub_goods_count"];
			$code = $g["code"];
			$name = $g["name"];
			$spec = $g["spec"];
			$unitName = $g["unit_name"];
			
			// 查询商品销售价格
			$salePrice = 0;
			if ($customerId) {
				$price = $this->getPriceInPriceSystem($db, $customerId, $goodsId);
				if ($price) {
					$salePrice = $price;
				} else {
					// 没有设置价格体系，就先查询有没有历史销售价格
					$sql = "select sale_price
							from t_customer_saleprice
							where customer_id = '%s' and goods_id = '%s' ";
					$q = $db->query($sql, $customerId, $goodsId);
					if ($q) {
						$salePrice = $q[0]["sale_price"];
					} else {
						// 也没有历史销售价格，就取基础数据里面的销售价格
						$salePrice = $g["sale_price"];
					}
				}
			} else {
				$salePrice = $g["sale_price"];
			}
			
			$bom = array(
					"id" => $goodsId,
					"count" => $subCount
			);
			$bomList[] = $bom;
			
			$sql = "select v.id, v.balance_count, v.begin_dt, v.expiration, v.end_dt
					from t_inventory_lot v
					where v.warehouse_id = '%s' and v.goods_id = '%s' 
						and v.balance_count > 0 ";
			$data = $db->query($sql, $warehouseId, $goodsId);
			if ($data) {
				$totalCount = $bomCount * $subCount;
				
				foreach ( $data as $v ) {
					$qcBeginDT = $this->toYmdForQC($v["begin_dt"]);
					if ($qcBeginDT) {
						$expiration = $v["expiration"];
						$qcEndDT = $this->toYmdForQC($v["end_dt"]);
					} else {
						$expiration = null;
						$qcEndDT = null;
					}
					
					$invCount = $v["balance_count"];
					$c = 0;
					if ($invCount >= $totalCount) {
						$c = $totalCount;
						$totalCount = 0;
					} else {
						$totalCount = $totalCount - $invCount;
						$c = $invCount;
					}
					$item = array(
							"id" => $index ++,
							"goodsId" => $goodsId,
							"code" => $code,
							"name" => $name,
							"spec" => $spec,
							"unitName" => $unitName,
							"invCount" => $v["balance_count"],
							"goodsCount" => $c,
							"qcBeginDT" => $qcBeginDT,
							"qcEndDT" => $qcEndDT,
							"expiration" => $expiration,
							"price" => $salePrice
					);
					
					$result[] = $item;
				}
			} else {
				// 没有库存
				$item = array(
						"id" => $index ++,
						"goodsId" => $goodsId,
						"code" => $code,
						"name" => $name,
						"spec" => $spec,
						"unitName" => $unitName,
						"invCount" => 0,
						"goodsCount" => $subCount * $bomCount,
						"price" => $salePrice
				);
				
				$result[] = $item;
			}
		}
		
		return array(
				"dataList" => $result,
				"bomList" => $bomList
		);
	}

	/**
	 * 从售后单创建销售订单
	 */
	public function createSOBillFromASBill($asBillId, $db) {
		if ($db == null) {
			$db = M();
		}
		
		$idGen = new IdGenService();
		$us = new UserService();
		$dataOrg = $us->getLoginUserDataOrg();
		$companyId = $us->getCompanyId();
		$userId = $us->getLoginUserId();
		
		$ref = $this->genNewBillRef();
		$id = $idGen->newId($db);
		
		// 主表
		$sql = "select customer_id, out_warehouse_id, bizdt,
					biz_user_id, receiving_type, sales_mode_id
				from t_as_bill
				where id = '%s' ";
		$data = $db->query($sql, $asBillId);
		if (! $data) {
			return $this->bad("售后单不存在");
		}
		$v = $data[0];
		$customerId = $v["customer_id"];
		$warehouseId = $v["out_warehouse_id"];
		$bizDT = $this->toYMD($v["bizdt"]);
		$bizUserId = $v["biz_user_id"];
		$receivingType = $v["receiving_type"];
		$salesModeId = $v["sales_mode_id"];
		
		$sql = "select province, city, district, tel01, fax, address,
					mobile01, taobao_nickname, name
				from t_customer
				where id = '%s' ";
		$data = $db->query($sql, $customerId);
		if (! $data) {
			return $this->bad("客户不存在");
		}
		$v = $data[0];
		$province = $v["province"];
		$city = $v["city"];
		$district = $v["district"];
		$contact = $v["name"];
		$nickname = $v["taobao_nickname"];
		if ($nickname) {
			$contact = $contact . " / " . $nickname;
		}
		$tel = $v["tel01"];
		$mobile = $v["mobile01"];
		if ($tel) {
			if ($mobile) {
				$tel = $tel . " / " . $mobile;
			}
		} else {
			$tel = $mobile;
		}
		
		$fax = $v["fax"];
		$address = $v["address"];
		
		$sql = "select org_id
				from t_user
				where id = '%s' ";
		$data = $db->query($sql, $bizUserId);
		if (! $data) {
			return $this->bad("业务员不存在");
		}
		$orgId = $data[0]["org_id"];
		
		// TODO: 默认的快递公司是什么呢？
		$expressCompanyId = '';
		$sql = "select id from t_express_company
				order by name limit 1";
		$data = $db->query($sql);
		if ($data) {
			$expressCompanyId = $data[0]["id"];
		}
		
		$sql = "insert into t_so_bill(id, bill_status, biz_dt, deal_date, org_id, biz_user_id,
					date_created, goods_money, tax, money_with_tax, input_user_id, ref,
					customer_id, contact, tel, fax, deal_address, bill_memo, receiving_type,
					data_org, company_id, warehouse_id, sales_mode_id, express_company_id,
					freight, deal_province, deal_city, deal_district)
				values ('%s', 0, '%s', '%s', '%s', '%s',
					now(), %f, %f, %f, '%s', '%s',
					'%s', '%s', '%s', '%s', '%s', '%s', %d,
					'%s', '%s', '%s', '%s', '%s',
					0, '%s', '%s', '%s')";
		$db->execute($sql, $id, $bizDT, $bizDT, $orgId, $bizUserId, 0, 0, 0, $userId, $ref, 
				$customerId, $contact, $tel, $fax, $address, '', $receivingType, $dataOrg, 
				$companyId, $warehouseId, $salesModeId, $expressCompanyId, $province, $city, 
				$district);
		
		// 取得税率
		$bcs = new BizConfigService();
		$taxRate = $bcs->getTaxRate();
		
		// 明细表
		$sql = "select goods_id, goods_count, goods_price, goods_money, 
					qc_begin_dt, expiration, qc_end_dt
				from t_as_bill_ex_detail
				where asbill_id = '%s'
				order by show_order";
		$data = $db->query($sql, $asBillId);
		foreach ( $data as $i => $v ) {
			$goodsId = $v["goods_id"];
			$goodsCount = $v["goods_count"];
			$goodsPrice = $v["goods_price"];
			$goodsMoney = $v["goods_money"];
			$tax = $goodsMoney * $taxRate / 100;
			$moneyWithTax = $goodsMoney + $tax;
			$qcBeginDT = $this->toYmdForQC($v["qc_begin_dt"]);
			if ($qcBeginDT == null) {
				$qcBeginDT = "1970-01-01";
			}
			$expiration = $v["expiration"];
			$qcEndDT = $this->toYmdForQC($v["qc_end_dt"]);
			if ($qcEndDT == null) {
				$qcEndDT = "1970-01-01";
			}
			
			$sql = "insert into t_so_bill_detail (id, date_created, goods_id, goods_count, goods_price,
						goods_money, sobill_id, tax_rate, tax, money_with_tax, ws_count, left_count,
						show_order, data_org, company_id, qc_begin_dt, expiration, qc_end_dt)
					values ('%s', now(), '%s', %d, %f,
						%f, '%s', %d, %f, %f, 0, %d,
						%d, '%s', '%s', '%s', %d, '%s')";
			$db->execute($sql, $idGen->newId($db), $goodsId, $goodsCount, $goodsPrice, $goodsMoney, 
					$id, $taxRate, $tax, $moneyWithTax, $goodsCount, $i, $dataOrg, $companyId, 
					$qcBeginDT, $expiration, $qcEndDT);
		}
		
		// 金额合计
		$sql = "select sum(goods_money) as sum_goods_money, 
					sum(tax) as sum_tax,
					sum(money_with_tax) as sum_money_with_tax
				from t_so_bill_detail
				where sobill_id = '%s' ";
		$data = $db->query($sql, $id);
		if ($data) {
			$sumGoodsMoney = $data[0]["sum_goods_money"];
			$sumTax = $data[0]["sum_tax"];
			$sumMoneyWithTax = $data[0]["sum_money_with_tax"];
			
			$sql = "update t_so_bill
					set goods_money = %f, tax = %f, money_with_tax = %f
					where id = '%s' ";
			$db->execute($sql, $sumGoodsMoney, $sumTax, $sumMoneyWithTax, $id);
		}
		
		// 关联 售后单和销售订单
		$sql = "update t_as_bill
				set gen_sobill_id = '%s', gen_sobill_ref = '%s'
				where id = '%s' ";
		$db->execute($sql, $id, $ref, $asBillId);
		
		return $this->ok();
	}
}